USE [BS1]
GO
/****** Object:  View [dbo].[VW_Vouchers]    Script Date: 11/12/2020 11:38:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[VW_Vouchers]
AS
SELECT     dbo.VoucherDetails.VoucherID, dbo.Voucher.VoucherDate, dbo.Voucher.VoucherNo, dbo.Voucher.SessionID, dbo.SessionInfo.SessionTitle, 
                      dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, dbo.SessionInfo.CurrentSession, dbo.Voucher.Remarks AS MasterRemarks, dbo.Voucher.ReadOnly, 
                      dbo.VoucherDetails.VoucherDetailID, dbo.VoucherDetails.AccountNo, dbo.Accounts.AccountTitle, dbo.VoucherDetails.Remarks, 
                      CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') THEN dbo.VoucherDetails.Amount ELSE - 1 * dbo.VoucherDetails.Amount END AS Amount, 
                      CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Dr') THEN dbo.VoucherDetails.Amount ELSE 0 END AS Debit, CASE WHEN (dbo.VoucherDetails.DrOrCr = N'Cr') 
                      THEN dbo.VoucherDetails.Amount ELSE 0 END AS Credit, dbo.VoucherDetails.DrOrCr, dbo.Accounts.ParentAccount, dbo.Voucher.BankAccountID, 
                      Accounts_2.AccountTitle AS BankAccountTitle, Accounts_2.Remarks AS BankAddress, Accounts_2.CellNo AS BankCellNo, Accounts_2.Phone AS BankPhone, 
                      dbo.Voucher.LoginID, dbo.Employees.EmployeeName, dbo.Voucher.HostName, dbo.Voucher.EntryDateTime, dbo.VoucherDetails.Amount AS ActualAmount, 
                      dbo.Voucher.Amount AS VoucherAmount, dbo.Voucher.Expense, dbo.Voucher.TotalAmount, dbo.Voucher.Discount, dbo.Voucher.NetAmount, dbo.Voucher.ModifyID, 
                      Employees_1.EmployeeName AS ModifyName, dbo.Voucher.ModifyHostName, dbo.Voucher.ModifyDateTime, dbo.VoucherDetails.ItemID, dbo.vw_Items.ItemCode, 
                      dbo.vw_Items.ItemName, dbo.vw_Items.ItemNameUrdu, dbo.VoucherDetails.Qty, dbo.VoucherDetails.Rate, dbo.vw_Items.GroupID, dbo.vw_Items.GroupName, 
                      dbo.Voucher.CustomerName, dbo.VoucherDetails.Margin, dbo.VoucherDetails.SRate, dbo.Accounts.Remarks AS CustomerAddress, dbo.Accounts.CellNo, 
                      dbo.Accounts.Phone, dbo.Accounts.NameUrdu, dbo.Accounts.WaldUrdu, dbo.Accounts.QOM, dbo.Accounts.WorkType, dbo.Accounts.ZaminName1, 
                      dbo.Accounts.ZaminWald1, dbo.Accounts.ZaminQOM1, dbo.Accounts.ZaminWork1, dbo.Accounts.ZaminAddress1, dbo.Accounts.ZaminName2, 
                      dbo.Accounts.ZaminWald2, dbo.Accounts.ZaminQOM2, dbo.Accounts.ZaminWork2, dbo.Accounts.ZaminAddress2, dbo.Voucher.SerialNo, dbo.Voucher.ModelNo, 
                      dbo.Voucher.ReceiptNo, dbo.Voucher.DateDelivery, dbo.Voucher.MonthRent, dbo.Voucher.Security, dbo.Voucher.NextRdng, dbo.vw_Items.UnitName, 
                      Accounts_2.AreaID, dbo.Area.AreaCode, dbo.Area.AreaName, dbo.Area.AreaNameUrdu, dbo.Area.RegionID, dbo.Region.RegionCode, dbo.Region.RegionName, 
                      dbo.Region.RegionNameUrdu
FROM         dbo.Region RIGHT OUTER JOIN
                      dbo.Employees RIGHT OUTER JOIN
                      dbo.Voucher LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 LEFT OUTER JOIN
                      dbo.Area ON Accounts_2.AreaID = dbo.Area.AreaID ON dbo.Voucher.BankAccountID = Accounts_2.AccountNo LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 ON dbo.Voucher.ModifyID = Employees_1.EmployeeID ON dbo.Employees.EmployeeID = dbo.Voucher.LoginID RIGHT OUTER JOIN
                      dbo.vw_Items RIGHT OUTER JOIN
                      dbo.VoucherDetails ON dbo.vw_Items.ItemID = dbo.VoucherDetails.ItemID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.VoucherDetails.AccountNo = dbo.Accounts.AccountNo ON dbo.Voucher.VoucherID = dbo.VoucherDetails.VoucherID LEFT OUTER JOIN
                      dbo.SessionInfo ON dbo.Voucher.SessionID = dbo.SessionInfo.SessionID ON dbo.Region.RegionID = dbo.Area.RegionID


---------------------------------------------------------------------------